/** 
 * @Title: BrandFor3857oDao.java 
 * @Package com.zizo.autohome.word.3857o.dao 
 * @Description: TODO
 * @author HeYan
 * @date 2016年9月7日 下午2:54:49 
 */
package com.zizo.autohome.word.flowtrend.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;

import com.zizo.autohome.sta.slrank.util.SlrankUtil;
import com.zizo.autohome.word.flowtrend.bean.All;
import com.zizo.autohome.word.flowtrend.bean.LanNiao;
import com.zizo.util.ConnectionPool;
import com.zizo.util.Util;

/**
 * @ClassName: BrandFor3857oDao
 * @Description: TODO
 * @author HeYan
 * @date 2016年9月7日 下午2:54:49
 * 
 */
public class AllOutDaoOnlyKB implements Runnable {
	private Connection conn168;
	private Connection conn198;
	private All all;
	private List<List<String>> words;
	private Map<String, String> level;// 城市线级和区域
	private String tablename;

	public AllOutDaoOnlyKB(Connection conn168, Connection conn198, All all, List<List<String>> words,
			Map<String, String> level, String tablename) {
		super();
		this.conn168 = conn168;
		this.conn198 = conn198;
		this.all = all;
		this.words = words;
		this.level = level;
		this.tablename = tablename;
	}

	/**
	 * @Title: isSubSet
	 * @Description: TODO 判断l2是否为l1的子集
	 * @param l1
	 * @param l2
	 * @return
	 * @return boolean
	 * @author Alexis
	 */
	public boolean isSubSet(List<String> l1, List<String> l2) {
		boolean flag = true;
		for (int i = 2; i < 6; i++) {
			String w1 = l1.get(i);
			String w2 = l2.get(i);
			flag = w1.contains(w2);
			if (!flag) {
				break;
			}
		}
		return flag;
	}

	/**
	 * @Title: isContains
	 * @Description: TODO 判断给定的句子是否包含特定的关键词，包含则插入到数据库中
	 * @param content
	 * @param type
	 *            0---bbs 1--kb
	 * @return void
	 * @author Alexis
	 */
	public void isContains(String content, int type, String rank, String area, String time, String pro, String city,
			int id) {
		List<List<String>> total = new ArrayList<>();
		for (List<String> list : words) {
			boolean t = false;
			for (List<String> l : total) {
				t = isSubSet(l, list);
				if (t) {
					break;
				}
			}
			if (t)
				continue;
			boolean flag = true;
			StringBuffer sb = new StringBuffer();
			for (int i = 2; i < 6; i++) {
				if (!content.contains(list.get(i))) {
					flag = false;
					break;
				} else {
					sb.append(list.get(i) + "  ");
				}
			}
			if (flag) {
				LanNiao lan = new LanNiao(all.getBid(), getBizID(Integer.parseInt(list.get(0))), time, content, pro,
						city, id, list.get(1));
				String word = sb.toString();
				if (type == 0) {
					insertLania(lan, rank, area, word.trim());
				} else {
					insertLaniaKB(lan, rank, area, word.trim());
				}
				total.add(list);
			}
		}
	}

	/**
	 * @Title: getAllIDs
	 * @Description: TODO 得到总表的信息
	 * @return void
	 * @author HeYan
	 */
	public List<All> getAllIDs() {
		List<All> list = new ArrayList<>();
		Statement st = null;
		String sql = "SELECT DISTINCT BizID,CarID,ClubID,CarName FROM Projects_2016.brand_models_alias_new";
		ResultSet rs = null;
		try {
			st = conn198.createStatement();
			rs = st.executeQuery(sql);
			while (rs.next()) {
				int carid = rs.getInt("CarID");
				int clubid = rs.getInt("ClubID");
				String carname = rs.getString("CarName");
				All all = new All(rs.getInt("BizID"), carid, clubid, null, carname);
				list.add(all);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	public final void getDataFromBBS() {
		Statement st = null;
		ResultSet rs = null;
		String sql = "SELECT bbs_title,bbs_content,bbs_ptime,bbs_sf,bbs_cs,bbs_bbsid FROM autohome_syn.bbs_c_"
				+ all.getClubid() + " WHERE bbs_ptime > '2015-10-26'";
		try {
			st = conn168.createStatement();
			rs = st.executeQuery(sql);
			while (rs.next()) {
				String bbs_bt = rs.getString("bbs_title");
				String bbs_nr = rs.getString("bbs_content");
				String bbs_ftsj = rs.getString("bbs_ptime");
				String bbs_sf = rs.getString("bbs_sf");
				String bbs_ds = rs.getString("bbs_cs");
				int bbs_id = rs.getInt("bbs_bbsid");
				String rank = "";
				String area = "";
				try {
					if (bbs_ds.length() > 1) {
						rank = level.get(bbs_ds).split("-")[0];
						area = level.get(bbs_ds).split("-")[1];
					} else {
						if (!bbs_sf.equals("其他")) {
							rank = level.get(bbs_sf).split("-")[0];
							area = level.get(bbs_sf).split("-")[1];
						}
					}
				} catch (Exception e) {
				}
				String[] ns = bbs_bt.split("\n");
				for (String s : ns) {
					String[] bts = s.split("\\}\\{");
					for (String bt : bts) {
						for (String str : bt.split("\\?")) {
							isContains(str, 0, rank, area, bbs_ftsj, bbs_sf, bbs_ds, bbs_id);
						}
					}
				}
				String[] nss = bbs_nr.split("\n");
				for (String s : nss) {
					String[] nrs = s.split("\\}\\{");
					for (String nr : nrs) {
						for (String str : nr.split("\\?")) {
							isContains(str, 0, rank, area, bbs_ftsj, bbs_sf, bbs_ds, bbs_id);
						}
					}
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public int getBizID(int carid) {
		Statement st = null;
		ResultSet rs = null;
		String sql = "select brand_id from carvoice_biz.biz_brand where brand_autohome=" + carid;
		int res = 0;
		try {
			st = conn168.createStatement();
			rs = st.executeQuery(sql);
			if (rs.next()) {
				res = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return res;
	}

	public final void getDataFromKB() {
		Statement st = null;
		ResultSet rs = null;
		String sql = "SELECT kb_content,kb_ptime,kb_province,kb_area,kb_kbid FROM autohome_syn.autohome_kb"
				+ " WHERE kb_ptime > '2015-10-26' AND kb_mid=" + all.getCarid();
		try {
			st = conn168.createStatement();
			rs = st.executeQuery(sql);
			while (rs.next()) {
				String bbs_nr = rs.getString("kb_content");
				String bbs_ftsj = rs.getString("kb_ptime");
				String bbs_sf = rs.getString("kb_province");
				String bbs_ds = rs.getString("kb_area");
				int kb_id = rs.getInt("kb_kbid");
				String rank = "";
				String area = "";
				if (bbs_sf.length() > 1) {
					if (!bbs_sf.equals("其他")) {
						rank = level.get(bbs_sf).split("-")[0];
						area = level.get(bbs_sf).split("-")[1];
					}
				}
				String[] ns = bbs_nr.split("\n");
				for (String s : ns) {
					String[] bts = s.split("\\}\\{");
					for (String bt : bts) {
						for (String str : bt.split("\\?")) {
							isContains(str, 1, rank, area, bbs_ftsj, bbs_sf, bbs_ds, kb_id);
						}
					}
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public List<List<String>> getCarAndAlias() {
		List<List<String>> list = new ArrayList<>();
		Statement st = null;
		ResultSet rs = null;
		String sql = "SELECT CarID,CarName,Alias_1,Alias_2,Alias_3,Alias_4 FROM Projects_2016.brand_models_alias_new";
		try {
			st = conn198.createStatement();
			rs = st.executeQuery(sql);
			while (rs.next()) {
				int carid = rs.getInt("CarID");
				String carname = rs.getString("CarName");
				String w1 = rs.getString(3);
				String w2 = rs.getString(4);
				String w3 = rs.getString(5);
				String w4 = rs.getString(6);
				if (w1 == null || w1.equals("")) {
					w1 = "";
				}
				if (w2 == null || w2.equals("")) {
					w2 = "";
				}
				if (w3 == null || w3.equals("")) {
					w3 = "";
				}
				if (w4 == null || w4.equals("")) {
					w4 = "";
				}
				List<String> l = new ArrayList<>();
				l.add(carid + "");
				l.add(carname);
				l.add(w1);
				l.add(w2);
				l.add(w3);
				l.add(w4);
				list.add(l);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		for (int i = 0; i < list.size(); i++) {
			for (int j = i + 1; j < list.size(); j++) {
				List<String> l1 = list.get(i);
				List<String> l2 = list.get(j);
				if (getLenth(l1) < getLenth(l2)) {
					List<String> temp = l1;
					list.set(i, l2);
					list.set(j, temp);
				}
			}
		}
		return list;
	}

	public int getLenth(List<String> list) {
		int length = 0;
		StringBuffer sb = new StringBuffer();
		for (int i = 2; i < 6; i++) {
			sb.append(list.get(i));
		}
		length = sb.length();
		return length;
	}

	public void createTable() {
		Statement st = null;
		String sql1 = "CREATE TABLE all_in_out_kb_" + tablename + " LIKE all_in_out_kb;";
		try {
			st = conn198.createStatement();
			st.executeUpdate(sql1);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void insertLaniaKB(LanNiao lan, String rank, String area, String word) {
		PreparedStatement ps = null;
		String sql = "INSERT INTO Projects_2016.all_in_out_kb_" + tablename
				+ "(flowtrend_cid,flowtrend_time,flowtrend_content,flowtrend_province,flowtrend_area,flowtrend_carid,flowtrend_cidname,flowtrend_kbid,flowtrend_carname,flowtrend_level,flowtrend_region,flowtrend_word) VALUES(?,?,?,?,?,?,?,?,?,?,?,?)";
		try {
			ps = conn198.prepareStatement(sql);
			ps.setInt(1, lan.getCid());
			ps.setTimestamp(2, (Timestamp) Util.getTimeHMS(lan.getTime()));
			ps.setString(3, lan.getContent());
			ps.setString(4, lan.getProvince());
			ps.setString(5, lan.getArea());
			ps.setInt(6, lan.getCarid());
			ps.setString(7, all.getCarname());
			ps.setInt(8, lan.getId());
			ps.setString(9, lan.getCarname());
			ps.setString(10, rank);
			ps.setString(11, area);
			ps.setString(12, word);
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	// BBS
	public void insertLania(LanNiao lan, String rank, String area, String word) {
		PreparedStatement ps = null;
		String sql = "INSERT INTO Projects_2016.all_in_out_bbs_" + tablename
				+ "(flowtrend_cid,flowtrend_time,flowtrend_content,flowtrend_province,flowtrend_area,flowtrend_carid,flowtrend_cidname,flowtrend_bbsid,flowtrend_carname,flowtrend_level,flowtrend_region,flowtrend_word) VALUES(?,?,?,?,?,?,?,?,?,?,?,?)";
		try {
			ps = conn198.prepareStatement(sql);
			ps.setInt(1, lan.getCid());
			ps.setTimestamp(2, (Timestamp) Util.getTimeHMS(lan.getTime()));
			ps.setString(3, lan.getContent());
			ps.setString(4, lan.getProvince());
			ps.setString(5, lan.getArea());
			ps.setInt(6, lan.getCarid());
			ps.setString(7, all.getCarname());
			ps.setInt(8, lan.getId());
			ps.setString(9, lan.getCarname());
			ps.setString(10, rank);
			ps.setString(11, area);
			ps.setString(12, word);
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void run() {
		getDataFromKB();
		System.out.println(all.getCarid());
	}

	public static void main(String[] args) {
		ExecutorService fix = Executors.newFixedThreadPool(20);// 50个线程
		Connection conn198 = ConnectionPool.getConnection("Projects_2016", "localhost", "3306");
		Connection conn168 = ConnectionPool.getConnection("autohome_syn", "192.169.0.168", "3306");
		String tablename = "1201_chuang";
		AllOutDaoOnlyKB dao = new AllOutDaoOnlyKB(conn168, conn198, null, null, null, tablename);
		dao.createTable();
		Map<String, String> level = SlrankUtil.getCityLevel();
		List<List<String>> list = dao.getCarAndAlias();
		int[] s = { 24016, 26920, 24017, 22462, 24018, 24236, 24234, 24235, 22461 };
		String[] name = { "卡罗拉 2016款 双擎 1.8L CVT先锋版", "卡罗拉 2016款 双擎 1.8L CVT领先版", "卡罗拉 2016款 双擎 1.8L CVT精英版",
				"卡罗拉 2016款 双擎 1.8L CVT豪华版", "卡罗拉 2016款 双擎 1.8L CVT旗舰版", "雷凌 2016款 双擎 1.8H GS CVT精英版",
				"雷凌 2016款 双擎 1.8H GS CVT精英天窗版", "雷凌 2016款 双擎 1.8H GS-L CVT领先版", "雷凌 2016款 双擎 1.8H V CVT豪华版" };
		for (int i = 0; i < 9; i++) {
			All all = new All(s[i], s[i], s[i], "", name[i]);
			fix.execute(new AllOutDaoOnlyKB(conn168, conn198, all, list, level, tablename));
		}
		fix.shutdown();
	}
}
